package com.skycode.dao;

import com.skycode.bean.NewsBean;
import com.skycode.util.DBConnection;
import com.skycode.util.Time;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NewsDao {
    DBConnection DBConn = new DBConnection();

    public List<NewsBean> getList(int page){
        Connection connection = DBConn.getConn();
        String sql = "select * from news limit "+(page-1)*10+" 10";
        List<NewsBean> list = new ArrayList<NewsBean>();

        try {
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                NewsBean newsBean = new NewsBean();
                newsBean.setNewsID(rs.getInt("NewsID"));
                newsBean.setNewsTitle(rs.getString("NewsTitle"));
                newsBean.setNewsContent(rs.getString("NewsContent"));
                newsBean.setNewsTime(rs.getString("NewsTime"));
                newsBean.setAdminName(rs.getString("AdminName"));
                list.add(newsBean);
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 添加新闻
     * @param news
     * @return
     */
    public int add(NewsBean news){
        Connection conn = DBConn.getConn();
        String sql = "INSERT into news value(null,?,?,?,?)";
        try{
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1,news.getNewsTitle());
            pmst.setString(2,news.getNewsContent());
            pmst.setString(3,(Time.getDate());
            pmst.setString(4,news.getAdminName());
            return pmst.executeUpdate();//执行添加 返回受影响的行数
        } catch (SQLException e){
            e.printStackTrace();
        }
        return  0;//添加失败返回0
    }
    public int update(NewsBean news){
        Connection conn = DBConn.getConn();
        String sql = "update news set NewsTitle=?,NewsContent=?,NewsTime=?,AdminName=? where NewsID=?";
        try{
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1,news.getNewsTitle());
            pmst.setString(2,news.getNewsContent());
            pmst.setString(3,(Time.getDate());
            pmst.setString(4,news.getAdminName());
            pmst.setInt(5,news.getNewsID());
            return pmst.executeUpdate();//执行添加 返回受影响的行数
        } catch (SQLException e){
            e.printStackTrace();
        }
        return  0;//添加失败返回0
    }
    public int delete(int newsID){
        Connection conn = DBConn.getConn();
        try{
            String sql = "delete from news where NewsID=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1,newsID);
            return pmst.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }

    /**
     *  统计新闻总条数
     * @return
     */
    public int count(){
        Connection conn = DBConn.getConn();
        try {
            String sql = "select count(*) from news";
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
         if (rs.next()){
             return rs.getInt(1);// count查询返回一行数据 第一行第一列就是总条数
         }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return  0;
    }
}
